package com.hutao.manage.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcelUtil {

	public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
	public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";
	
	
	
	private static NumberFormat numberFormat = NumberFormat.getNumberInstance();
	
	static {
		    numberFormat.setGroupingUsed(false);
	
	}
	

	
	
	public static void main(String[] args) throws IOException {
		readExecl("D://执业护士编码.xls");
	}

	/**
	 * 
	 * <p>
	 * Title: readExecl
	 * </p>
	 * <p>
	 * Description:读取Execl
	 * </p>
	 * 
	 * @author zhouaokai
	 */
	public static List<Map<String,Object>> readExecl(String path) throws IOException {
		File file = new File(path);
		List<Map<String,Object>> list = null;
		if (file.exists()) {
			String[] split = path.split("\\.");
			if (OFFICE_EXCEL_2003_POSTFIX.equals(split[split.length - 1])) {
				list = readXls(path);
			} else if (OFFICE_EXCEL_2010_POSTFIX.equals(split[split.length - 1])) {
				list = readXlsx(path);
			}
		}

		return list;
	}

	/**
	 * 
	 * <p>
	 * Title: readExecl
	 * </p>
	 * <p>
	 * Description:读取Execl
	 * </p>
	 * 
	 * @author zhouaokai
	 */
	public static List<Map<String,Object>> readXlsx(String path) throws IOException {
		FileInputStream is = new FileInputStream(path);
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
		int numberOfSheets = xssfWorkbook.getNumberOfSheets();
		numberOfSheets=1;
		XSSFRow xssfRow = null;
		
		List<Map<String,Object>> mapList = new ArrayList<>();
		Map<String,Object> map = null;
		try {
			for (int s = 0; s < numberOfSheets; s++) {
				try {
					XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(s);
					for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
						map = new LinkedHashMap<>();
						xssfRow = xssfSheet.getRow(rowNum);
						if (xssfRow != null) {
							for (int i = 0; i < xssfRow.getLastCellNum(); i++) {
								XSSFCell cell = xssfRow.getCell(i);
								if (cell == null) {
									map.put(i+"", null);
								}else {
									String value = getValue(cell);
									map.put(i+"", value);
								}
								
								
								
								
							}
						}
						
						mapList.add(map);
						
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		} catch (NumberFormatException e) {
			e.printStackTrace();
		} finally {
			is.close();
		}
		return mapList;
	}

	/**
	 * 
	 * <p>
	 * Title: readExecl
	 * </p>
	 * <p>
	 * Description:读取Execl
	 * </p>
	 * 
	 * @author zhouaokai
	 */
	public static List<Map<String,Object>> readXls(String path) throws IOException {
		List<Map<String,Object>> mapList = new ArrayList<>();
		FileInputStream is = new FileInputStream(path);
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
		int numberOfSheets = hssfWorkbook.getNumberOfSheets();
		Map<String,Object> map = null;
		try {
			for (int s = 0; s < numberOfSheets; s++) {
				try {
					HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(s);
					
					HSSFRow hssfRow = hssfSheet.getRow(0);
					System.out.println(hssfSheet.getPhysicalNumberOfRows());
					for (int rowNum = 1; rowNum <= hssfSheet.getPhysicalNumberOfRows(); rowNum++) {
						hssfRow = hssfSheet.getRow(rowNum);
						map = new LinkedHashMap<>();
						if (hssfRow != null) {
							for (int i = 0; i < hssfRow.getLastCellNum(); i++) {
								HSSFCell cell = hssfRow.getCell(i);
								if (cell == null) {
									map.put(i+"", null);
								}else {
									String value = getValue(cell);
									map.put(i+"", value);
								}
								
								
							}
						}else {
							continue;
						}
						mapList.add(map);
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		} catch (NumberFormatException e) {
			e.printStackTrace();
		} finally {
			is.close();
		}
		return mapList;
	}

	@SuppressWarnings({ "static-access" })
	private static String getValue(XSSFCell xssfRow) {
		// 将数字当成字符串来读，防止将1读取成1.0
		if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
			//xssfRow.setCellType(xssfRow.CELL_TYPE_STRING);
		}
		if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
			return String.valueOf(xssfRow.getBooleanCellValue());
		} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
			//return String.valueOf(xssfRow.getNumericCellValue());
			 double d = xssfRow.getNumericCellValue();
			 return new BigDecimal(String.valueOf(d)).stripTrailingZeros().toPlainString();
		} else {
			return String.valueOf(xssfRow.getStringCellValue());
		}
	}

	@SuppressWarnings({ "static-access" })
	private static String getValue(HSSFCell hssfCell) {
		// 将数字当成字符串来读，防止将1读取成1.0
		if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
			//hssfCell.setCellType(hssfCell.CELL_TYPE_STRING);
		}
		if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
	        double d = hssfCell.getNumericCellValue();
	        return new BigDecimal(String.valueOf(d)).stripTrailingZeros().toPlainString();
			//return String.valueOf(hssfCell.getNumericCellValue());
		} else {
			return String.valueOf(hssfCell.getStringCellValue());
		}
	}
	
	
	private String getStringCellValue(Cell cell) //获取excel单元格数据内容中为字符串类型的数据
    {
        String strCell = "";
        if(cell == null)
        {
            return "";

        }else
            //判断数据的类型  
            switch (cell.getCellType()){  
                case Cell.CELL_TYPE_NUMERIC: //数字  
                    DataFormatter dataFormatter = new DataFormatter();

                    strCell = dataFormatter.formatCellValue(cell);
                    //这行代码就可以完美解决数字的小数点显示问题及电话号等长数字的科学计数法显示问题
                     //注意不要使用HSSFDataFormatter方法，这只支持2003版的excel哦
                    break;  
                case Cell.CELL_TYPE_STRING: //字符串  
                    strCell = String.valueOf(cell.getStringCellValue());  
                    break;  
                case Cell.CELL_TYPE_BOOLEAN: //Boolean  
                    strCell = String.valueOf(cell.getBooleanCellValue());  
                    break;  
                case Cell.CELL_TYPE_FORMULA: //公式  
                    strCell = String.valueOf(cell.getCellFormula());  
                    break;  
                case Cell.CELL_TYPE_BLANK: //空值   
                    strCell = "";  
                    break;  
                case Cell.CELL_TYPE_ERROR: //故障  
                    strCell = "非法字符";  
                    break;  
                default:  
                    strCell = "未知类型";  
                    break;  
            }
        if(strCell.equals("")||strCell==null)
        {
            return "";
        }
        
        
        return strCell;
        
    }
	

}
